/*
 * This class will handle all connections to the DB, by checking if
 * the connection is still valid first, and if needed reconnecting
 * before returning the Connection.  You can get the connection by calling
 * the getConnection() method.  Make sure to Close the method using the
 * closeConnection() method.
 *
 * All of the static fields will be initialized when called by EPoverty.java
 * This allows the user to still adjust settings to make the initial connection
 * that verifies if they are an admin in the system!
 */
package epoverty;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.ResultSetMetaData;
import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.DateFormatSymbols;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import javax.imageio.ImageIO;

public class DBconnection
{
   //*********************************************STATIC VARIABLES USED FOR CONNECTION
   private static String url;
   private static String driver;
   private static String dbLogin;
   private static String dbPassword;
   private static Connection conn;
   private static PreparedStatement stmt;
   private static ResultSet rs;

   public DBconnection(String url, String driver, String dbLogin, String dbPassword) throws ClassNotFoundException, SQLException
   {
      //******************************************INITIALIZE STATIC VARAIBLES FROM ABOVE
      DBconnection.url = url;
      DBconnection.driver = driver;
      DBconnection.dbLogin = dbLogin;
      DBconnection.dbPassword = dbPassword;

      // Open and close the connection for a test
      Class.forName(driver);
      conn = (Connection) DriverManager.getConnection(url, dbLogin, dbPassword);
      conn.close();
   }

   //*********************************************CHECKS CONNECTION AND RETURNS IT
   public static Connection getConnection() throws SQLException, ClassNotFoundException
   {
      if (conn.isClosed())
      {
         //***************************************RE-ESTABLISH CONNECTION
         Class.forName(driver);
         conn = (Connection) DriverManager.getConnection(url, dbLogin, dbPassword);
         return conn;
      }
      else
      {
         return conn;
      }
   }

   //*********************************************CLOSES THE CONNECTION FOR SECURITY REASONS THIS SHOULD ALWAYS BE CALLED
   public static void closeConnection() throws SQLException
   {
      conn.close();
   }

   //*********************************************CLOSES THE STATEMENT SHOULD ALWAYS BE CALLED
   public static void closeStatement() throws SQLException
   {
      stmt.close();
   }

   //*********************************************CLOSES THE RESULT SET SHOULD ALWAYS BE CALLED
   public static void closeResultSet() throws SQLException
   {
      rs.close();
   }

   // ********************************************ADD A WITHDRAWAL FROM THE GIVEN ACCOUNT
   public static void insertWithdrawalFromAccount(int account, BigDecimal amount, String payee, String description, String date) throws SQLException, ClassNotFoundException, ParseException
   {
      String insertWithdrawal = "INSERT into withdrawals (accountId, amount, payee, description, withdrawalDate) values (?, ?, ?, ?, ?)";

      // CONVERT STRING INTO A DATE, THEN TO AN SQL DATE
      DateFormat formatter;
      java.util.Date payDate;
      formatter = new SimpleDateFormat("yyyy-MM-dd");
      payDate = (java.util.Date) formatter.parse(date);
      java.sql.Date sqlDate = new java.sql.Date(payDate.getTime());

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(insertWithdrawal);

      stmt.setInt(1, account);
      stmt.setBigDecimal(2, amount);
      stmt.setString(3, payee);
      stmt.setString(4, description);
      stmt.setDate(5, sqlDate);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();

      // CALL METHOD TO UPDATE ACCOUNTS TABLE WITH THE WITHDRAWAL
      updateAccountWithdrawals(account, amount);
   }

   // ********************************************UPDATES THE AMOUNTS IN THE TRIPS TABLE
   public static void updateAccountWithdrawals(int account, BigDecimal amount) throws SQLException, ClassNotFoundException
   {
      String updateAmount = "UPDATE accounts SET balance = ? WHERE accountId = " + account;
      String getAmount = "Select balance FROM accounts WHERE accountId = " + account;

      BigDecimal balance;

      // GET AMMOUNT FROM DB AND SUBTRACT THE WITHDRAWAL
      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(getAmount);
      rs = (ResultSet) stmt.executeQuery();
      rs.next();
      balance = new BigDecimal(rs.getDouble(1));

      balance = balance.subtract(amount);

      stmt = (PreparedStatement) conn.prepareStatement(updateAmount);
      stmt.setBigDecimal(1, balance);
      stmt.executeUpdate();

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   // ********************************************UPDATES THE AMOUNTS IN THE TRIPS TABLE
   public static void updateAccountDeposits(int account, BigDecimal amount) throws SQLException, ClassNotFoundException
   {
      String updateAmount = "UPDATE accounts SET balance = ? WHERE accountId = " + account;
      String getAmount = "Select balance FROM accounts WHERE accountId = " + account;

      BigDecimal balance;

      // GET AMMOUNT FROM DB AND SUBTRACT THE WITHDRAWAL
      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(getAmount);
      rs = (ResultSet) stmt.executeQuery();
      rs.next();
      balance = new BigDecimal(rs.getDouble(1));

      balance = balance.add(amount);

      stmt = (PreparedStatement) conn.prepareStatement(updateAmount);
      stmt.setBigDecimal(1, balance);
      stmt.executeUpdate();

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   // ********************************************UPDATES A FUNDRAISERS GOAL REACHED DAY
   public static void updateFundraisersGoalReached(int fundraiserPersonID, String date) throws SQLException, ClassNotFoundException, ParseException
   {
      // CONVERT STRING INTO A DATE, THEN TO AN SQL DATE
      DateFormat formatter;
      java.util.Date metDate;
      formatter = new SimpleDateFormat("yyyy-MM-dd");
      metDate = (java.util.Date) formatter.parse(date);
      java.sql.Date sqlDate = new java.sql.Date(metDate.getTime());

      int fundraiserID = DBconnection.getFundraiserID(fundraiserPersonID);
      String fundraiserQuery = "UPDATE fundraisers SET dateMetRaiseGoal = ? WHERE fundraiserId = ?";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraiserQuery);

      stmt.setDate(1, sqlDate);
      stmt.setInt(2, fundraiserID);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   // ********************************************GET FUNDRAISERS FOR A SPECIFIC TRIP
   public static Trips.myTable getFundraisersInfoByTrip(Trips.myTable tripsTable, int tripID) throws SQLException, ClassNotFoundException
   {
      DateFormatSymbols dfs = new DateFormatSymbols();
      String[] monthsByName = dfs.getMonths();

      // CLEAR THE TABLE
      tripsTable.getDataVector().removeAllElements();

      // QUERY TO GET FUNDRAISER INFO FOR TABLE
      String fundraiserInfoQuery = "SELECT p.firstName, p.lastName, f.raiseGoal, f.raised, CAST(DATE_FORMAT(f.dateMetRaiseGoal, "
         + "'%Y-%m-%d') AS CHAR) "
         + "as DatedateMetRaiseGoal "
         + "FROM persons AS p, fundraisers AS f "
         + "WHERE p.personId = f.personId "
         + "AND f.expeditionId = ?";

      // GET CONNECTION AND RUN QUERY FOR TABLE
      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraiserInfoQuery);
      stmt.setInt(1, tripID);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData tableMD = (ResultSetMetaData) rs.getMetaData();

      // PUT THE META DATA INTO THE TABLE
      int fundraiserColCount = tableMD.getColumnCount();
      String[] columnNames =
      {
         "NAME", "RAISE GOAL", "RAISE AMOUNT", "DATE MET GOAL"
      };
      tripsTable.setColumnIdentifiers(columnNames);

      while (rs.next())
      {
         Object[] data = new String[fundraiserColCount - 1];

         //Parse out date to return the month name instead of number
         if (rs.getString(5) != null)
         {
            String year = rs.getString(5).substring(0, rs.getString(5).indexOf('-'));
            String day = rs.getString(5).substring(rs.getString(5).lastIndexOf('-') + 1, rs.getString(5).length());
            String month = rs.getString(5).substring(rs.getString(5).indexOf('-') + 1, rs.getString(5).lastIndexOf('-'));
            if (year.equalsIgnoreCase("0000") || day.equalsIgnoreCase("00") || month.equalsIgnoreCase("00"))
            {
               data[0] = rs.getString(1).concat(" ").concat(rs.getString(2));
               data[1] = rs.getString(3);
               data[2] = rs.getString(4);
               data[3] = "NOT REACHED";
            }
            else
            {
               String monthName = monthsByName[Integer.parseInt(month) - 1];

               data[0] = rs.getString(1).concat(" ").concat(rs.getString(2));
               data[1] = rs.getString(3);
               data[2] = rs.getString(4);
               data[3] = monthName.concat(" " + day + ", " + year);
            }
         }
         else
         {
            data[0] = rs.getString(1).concat(" ").concat(rs.getString(2));
            data[1] = rs.getString(3);
            data[2] = rs.getString(4);
            data[3] = "NOT REACHED";
         }

         tripsTable.addRow(data);
      }

      return tripsTable;
   }
   
      // ********************************************GET ONLY FUNDRAISER NAMES BY A TRIP ID
   public static Trips.myTable getFundraisersByTrip(Trips.myTable tripsTable, int tripID) throws SQLException, ClassNotFoundException
   {

      DateFormatSymbols dfs = new DateFormatSymbols();
      String[] monthsByName = dfs.getMonths();

      // CLEAR THE TABLE
      tripsTable.getDataVector().removeAllElements();

      // QUERY TO GET FUNDRAISER INFO FOR TABLE
      String fundraiserInfoQuery = "SELECT CONCAT(p.firstName, ' ', p.lastName) AS name " 
         + "FROM persons AS p, fundraisers AS f "
         + "WHERE p.personId = f.personId "
         + "AND f.expeditionId = ?";

      // GET CONNECTION AND RUN QUERY FOR TABLE
      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraiserInfoQuery);
      stmt.setInt(1, tripID);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData tableMD = (ResultSetMetaData) rs.getMetaData();

      // PUT THE META DATA INTO THE TABLE
      int fundraiserColCount = tableMD.getColumnCount();
      String[] columnNames =
      {
         "FUNDRAISER"
      };
      tripsTable.setColumnIdentifiers(columnNames);

      while (rs.next())
      {
         Object[] data = new String[fundraiserColCount];
         data[0] = rs.getString(1);
         
         tripsTable.addRow(data);
      }

      return tripsTable;
   }
   
   // ********************************************GETS ALL THE FUNDRAISER ID'S THAT WILL BE RESET
   public static void getFundraisersToReset(int tripID) throws SQLException, ClassNotFoundException
   {
       // QUERY TO GET EVERY fundraiserId FOR THE GIVEN tripID
       String fundraiserQuery = "SELECT fundraiserId FROM fundraisers "
               + "WHERE expeditionId = ?";                     
            
       conn = DBconnection.getConnection();
       stmt = (PreparedStatement) conn.prepareStatement(fundraiserQuery);
       stmt.setInt(1, tripID);
       rs = (ResultSet) stmt.executeQuery();
       
       while (rs.next())
       {
           // Insert each person into expeditionHistory
           insertIntoExpeditionHistory(tripID, rs.getInt(1));
           
           // Reset fundraisers 'stats'
           resetFundraiser(rs.getInt(1));
       }
       
       DBconnection.closeResultSet();
       DBconnection.closeStatement();
       DBconnection.closeConnection();
   }
   
   // ******************************************** INSERTS FUNDRAISERS BEING RESET INTO expeditionhistory
   public static void insertIntoExpeditionHistory(int tripID, int fundraiserID) throws SQLException, ClassNotFoundException
   {
       // QUERY TO MOVE PERSON INTO expeditionHistory
       String expeditionhistoryQuery = "INSERT into expeditionhistory (expeditionId, fundraiserId) "
               + "values (?, ?)";
       
       conn = DBconnection.getConnection();
       stmt = (PreparedStatement) conn.prepareStatement(expeditionhistoryQuery);
       stmt.setInt(1, tripID);
       stmt.setInt(2, fundraiserID);
       stmt.executeUpdate();
   }
   
   // ******************************************** METHOD THAT ACTUALLY RESETS THE FUNDRAISERS 'STATS'
   public static void resetFundraiser(int fundraiserID) throws SQLException, ClassNotFoundException
   {
       // QUERY TO RESET THE FUNDRAISERS 'STATS'
       String fundraiserUpdate = "UPDATE fundraisers SET expeditionId = 0, "
               + "raiseGoal = 100000, raised = 0, dateMetRaiseGoal = null "
               + "WHERE fundraiserId = ?";
       conn = DBconnection.getConnection();
       stmt = (PreparedStatement) conn.prepareStatement(fundraiserUpdate);
       stmt.setInt(1, fundraiserID);
       stmt.executeUpdate();
   }

   // ********************************************GET FUNDRAISER GOAL REACHED DAY FROM FUNDRAISERS TABLE
   public static String getFundraisersGoalReachedDay(int fundraiserPersonID) throws SQLException, ClassNotFoundException
   {
      String fundraiserQuery = "SELECT  CAST(dateMetRaiseGoal AS CHAR) as DatedateMetRaiseGoal FROM fundraisers WHERE fundraiserId = ?";
      int fundraiserID = DBconnection.getFundraiserID(fundraiserPersonID);

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraiserQuery);
      stmt.setInt(1, fundraiserID);
      rs = (ResultSet) stmt.executeQuery();

      if (rs.next())
      {
         if (rs.getString(1) != null)
         {
            return rs.getString(1);
         }
         else
         {
            return null;
         }
      }
      else
      {
         return null;
      }

   }

   // ********************************************UPDATES A FUNDRAISERS RAISED AMOUNT
   public static void updateFundraiserRaised(int fundraiserPersonID, BigDecimal amount) throws SQLException, ClassNotFoundException
   {
      int fundraiserID = DBconnection.getFundraiserID(fundraiserPersonID);
      String fundraiserQuery = "UPDATE fundraisers SET raised = ? WHERE fundraiserId = ?";
      String getRaisedQuery = "SELECT raised FROM fundraisers WHERE fundraiserId = ?";

      BigDecimal raised;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(getRaisedQuery);
      stmt.setInt(1, fundraiserID);
      rs = (ResultSet) stmt.executeQuery();
      rs.next();
      raised = new BigDecimal(rs.getDouble(1));

      amount = amount.add(raised);

      stmt = (PreparedStatement) conn.prepareStatement(fundraiserQuery);

      stmt.setBigDecimal(1, amount);
      stmt.setInt(2, fundraiserID);
      stmt.executeUpdate();

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   // ********************************************RETURNS THE PERCENT OF DONATIONS THAT GO TO OVERHEAD
   public static int getOverheadPercent() throws SQLException, ClassNotFoundException
   {
      String getPercent = "SELECT autoRedirectPercent FROM accounts where accountId = 1";
      int percent;
      double percentDecimal;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(getPercent);
      rs = (ResultSet) stmt.executeQuery();

      // GET THE DOUBLE FROM THE DATABASE
      rs.next();
      percentDecimal = rs.getDouble(1);

      //GET THE PERCENT IN xx% form
      percent = (int) (percentDecimal * 100);

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return percent;
   }

   // ********************************************SET THE OVERHEAD PERCENT
   public static void setOverheadPercent(double percent) throws SQLException, ClassNotFoundException
   {
      String setPercentOverhead = "UPDATE accounts SET autoRedirectPercent = ? WHERE accountId = 1";
      String setPercentOthers = "UPDATE accounts SET autoRedirectPercent = ? WHERE accountId = 2 OR accountId = 3";
      double overhead = (double) (percent / 100);
      double others = 1 - overhead;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(setPercentOverhead);
      stmt.setDouble(1, overhead);
      stmt.executeUpdate();

      stmt = (PreparedStatement) conn.prepareStatement(setPercentOthers);
      stmt.setDouble(1, others);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();

   }

   // ********************************************RETURNS AN ArrayList OF ALL TRIP INFORMATION
   public static ArrayList<Object> getTrips(int tripID) throws SQLException, ClassNotFoundException
   {
      ArrayList<Object> allTripData = new ArrayList<Object>();
      String getTripsQuery = "SELECT * FROM expeditions WHERE expeditionId = ?";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(getTripsQuery);
      stmt.setInt(1, tripID);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      // GET FUNDRAISERS AND STORE THEM IN AN ARRAYLIST
      while (rs.next())
      {
         ArrayList<Object> row = new ArrayList<Object>(columns);

         for (int i = 1; i <= columns; i++)
         {
            row.add(rs.getObject(i));
         }
         allTripData.add(row);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return allTripData;
   }

   //*********************************************RETURNS AN ArrayList OF ALL TRIPS THAT THEIR DEPART DATE HAS NOT PAST
   public static ArrayList<Object> getTrips() throws SQLException, ClassNotFoundException
   {
      ArrayList<Object> possibleTrips = new ArrayList<Object>();
      String getTripsQuery = "SELECT name, expeditionId FROM expeditions WHERE departDate >= CURRENT_TIMESTAMP";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(getTripsQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      // GET FUNDRAISERS AND STORE THEM IN AN ARRAYLIST
      while (rs.next())
      {
         ArrayList<Object> row = new ArrayList<Object>(columns);

         for (int i = 1; i <= columns; i++)
         {
            row.add(rs.getObject(i));
         }
         possibleTrips.add(row);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return possibleTrips;
   }
   
   // ********************************************CHECKS IF A TRIP IS ARCHIVABLE (has returned and not already in expeditionHistory)
   public static boolean isTripArchivable(int tripID) throws SQLException, ClassNotFoundException
   {
       boolean notArchived;
       boolean hasReturned;
       
       String expeditionQuery = "SELECT * FROM expeditionHistory WHERE "
               + "expeditionId = ?";
       conn = DBconnection.getConnection();
       stmt = (PreparedStatement) conn.prepareStatement(expeditionQuery);
       stmt.setInt(1, tripID);
       rs = (ResultSet) stmt.executeQuery();
       
       // If there are any results, trip has been archived
       if (rs.next())
       {
           notArchived = false;
       }
       else
       {
           notArchived = true;
       }
       
       expeditionQuery = "SELECT * FROM expeditions "
               + "WHERE expeditionId = ? "
               + "AND returnDate < CURRENT_TIMESTAMP";
       stmt = (PreparedStatement) conn.prepareStatement(expeditionQuery);
       stmt.setInt(1, tripID);
       rs = (ResultSet) stmt.executeQuery();
       
       // If there are any results, the trip has returned
       if (rs.next())
       {
           hasReturned = true;
       }
       else
       {
           hasReturned = false;
       }
       
       // If both the trip is not Archived (notArchived == true) AND the trip has returned (hasReturned == true) return true
       if (hasReturned && notArchived)
       {
           return true;
       }
       else
       {
           return false;
       }
   }
   
   //*********************************************RETURNS AN ArrayList OF ALL TRIPS THAT HAVE PAST THEIR DEPART DATE
   public static ArrayList<Object> getPastTrips() throws SQLException, ClassNotFoundException
   {
      ArrayList<Object> possibleTrips = new ArrayList<Object>();
      String getTripsQuery = "SELECT name, expeditionId FROM expeditions WHERE departDate < CURRENT_TIMESTAMP";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(getTripsQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      // GET FUNDRAISERS AND STORE THEM IN AN ARRAYLIST
      while (rs.next())
      {
         ArrayList<Object> row = new ArrayList<Object>(columns);

         for (int i = 1; i <= columns; i++)
         {
            row.add(rs.getObject(i));
         }
         possibleTrips.add(row);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return possibleTrips;
   }

   // ********************************************UPDATES A TRIPS SETTINGS
   public static void updateTrip(String expeditionID, String name, String expeditionDescription, String cost, 
           String defaultRaiseGoal, String departDate, String returnDate, 
           String cutoffDate) throws ParseException, SQLException, ClassNotFoundException
   {
      // CONVERT THE String to util.Date to an sql.Date
      DateFormat formatter;
      java.util.Date depart;
      java.util.Date retrn;
      java.util.Date cutoff;
      formatter = new SimpleDateFormat("yyyy-MM-dd");
      depart = (java.util.Date) formatter.parse(departDate);
      retrn = (java.util.Date) formatter.parse(returnDate);
      cutoff = (java.util.Date) formatter.parse(cutoffDate);
      java.sql.Date sqlDepart = new java.sql.Date(depart.getTime());
      java.sql.Date sqlReturn = new java.sql.Date(retrn.getTime());
      java.sql.Date sqlCutoff = new java.sql.Date(cutoff.getTime());

      String expeditionQuery = "UPDATE expeditions SET name = ?, expeditionDescription = ?, cost = ?, defaultRaiseGoal = ?,"
         + " departDate = ?, returnDate = ?, cutoffDate = ? WHERE expeditionId = " + expeditionID;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(expeditionQuery);

      stmt.setString(1, name);
      stmt.setString(2, expeditionDescription);
      stmt.setDouble(3, Double.parseDouble(cost));
      stmt.setDouble(4, Double.parseDouble(defaultRaiseGoal));
      stmt.setDate(5, sqlDepart);
      stmt.setDate(6, sqlReturn);
      stmt.setDate(7, sqlCutoff);

      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   // ********************************************GET A TRIPS DEFAULT RAISE GOAL FROM EXPEDITIONID
   public static String getRaiseGoal(int tripID) throws SQLException, ClassNotFoundException
   {
      String raiseGoal;
      String tripsQuery = "SELECT defaultRaiseGoal FROM expeditions WHERE expeditionId = ?";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(tripsQuery);
      stmt.setInt(1, tripID);
      rs = (ResultSet) stmt.executeQuery();
      rs.next();

      raiseGoal = rs.getString(1);

      return raiseGoal;
   }

   //*********************************************ADD PERSON TO DONORS VIA EMAIL
   public static void insertPersonIntoDonors(String email) throws SQLException, ClassNotFoundException
   {
      String addDonor = "INSERT into donors (personId) "
         + "SELECT p.personId FROM persons AS p "
         + "WHERE p.emailAddress = '" + email + "'";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(addDonor);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   //*********************************************ADD PERSON TO DONORS VIA EMAIL
   public static void insertPersonIntoDonors(int personID) throws SQLException, ClassNotFoundException
   {
      String addDonor = "INSERT into donors (personId) values (?)";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(addDonor);
      stmt.setInt(1, personID);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   //*********************************************ADD PERSON TO FUNDRAISERS VIA EMAIL
   public static void insertPersonIntoFundraisers(String email, String raiseGoal, int expeditionId) throws SQLException, ClassNotFoundException
   {
      String addDonor = "INSERT into fundraisers (personId, expeditionId, raiseGoal) "
         + "SELECT persons.personId,  '" + expeditionId + "', '" + raiseGoal + "' " + "FROM persons "
         + "WHERE persons.emailAddress = '" + email + "'";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(addDonor);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   //*********************************************ADD PERSON TO FUNDRAISERS VIA PERSONID
   public static void insertPersonIntoFundraisers(int personID, String raiseGoal, int expeditionID) throws SQLException, ClassNotFoundException
   {
      String addDonor = "INSERT into fundraisers (personId, raiseGoal, expeditionId) values (?, ?, ?)";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(addDonor);
      stmt.setInt(1, personID);
      stmt.setString(2, raiseGoal);
      stmt.setInt(3, expeditionID);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   //*********************************************ADD PERSON TO ADMINS VIA EMAIL
   public static void insertPersonIntoAdmins(String email) throws SQLException, ClassNotFoundException
   {
      String addDonor = "INSERT into admins (personId) "
         + "SELECT persons.personId FROM persons "
         + "WHERE persons.emailAddress = '" + email + "'";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(addDonor);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   //*********************************************ADD PERSON TO ADMINS VIA PERSONID
   public static void insertPersonIntoAdmins(int personID) throws SQLException, ClassNotFoundException
   {
      String addDonor = "INSERT into admins (personId) values (?)";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(addDonor);
      stmt.setInt(1, personID);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   //*********************************************ADDS A PERSON INTO THE PERSONS TABLE
   public static void addPerson(String fn, String mn, String ln, String pn, String ea, String ast, String ac, String as, String az,
                                BufferedImage photo, String pw, String notes, int referredBy) throws SQLException, ClassNotFoundException, IOException
   {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      ImageIO.write(photo, "jpg", baos);
      baos.flush();
      byte[] photoBytes = baos.toByteArray();
      String addPerson = "INSERT into persons (firstName, middleName, lastName, phoneNumber, emailAddress, addressStreet, "
         + "addressCity, addressState, addressZip, photo, password, notes, referredBy) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(addPerson);
      stmt.setString(1, fn);
      stmt.setString(2, mn);
      stmt.setString(3, ln);
      stmt.setString(4, pn);
      stmt.setString(5, ea);
      stmt.setString(6, ast);
      stmt.setString(7, ac);
      stmt.setString(8, as);
      stmt.setString(9, az);
      stmt.setBytes(10, photoBytes);
      stmt.setString(11, pw);
      stmt.setString(12, notes);
      stmt.setInt(13, referredBy);
      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }
   
   // ********************************************ADDS A PHOTO TO A SPECIFIC TRIP
   public static void addTripPhoto(int tripID, BufferedImage photo) throws IOException, SQLException, ClassNotFoundException
   {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      ImageIO.write(photo, "jpg", baos);
      baos.flush();
      byte[] photoBytes = baos.toByteArray();
      
      String expeditionPhotoQuery = "INSERT INTO expeditionPhotos (photo, expeditionId) values (?, ?)";
      
      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(expeditionPhotoQuery);
      stmt.setBytes(1, photoBytes);
      stmt.setInt(2, tripID);
      stmt.executeUpdate();
      
      DBconnection.closeStatement();
      DBconnection.closeConnection();
      
   }

   // ********************************************GET ALL THE PHOTOS FROM A TRIP
   public static ArrayList<BufferedImage> getTripPhotos(int tripID) throws SQLException, ClassNotFoundException, IOException
   {
      
      String expeditionsQuery = "SELECT photo FROM expeditionPhotos WHERE expeditionId = ?";
      ArrayList<BufferedImage> photosList = new ArrayList<BufferedImage>();

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(expeditionsQuery);
      stmt.setInt(1, tripID);
      rs = (ResultSet) stmt.executeQuery();

      while (rs.next())
      {
         byte[] photoBytes = rs.getBytes(1);//gets the person's photo as an array of bytes
         if (photoBytes != null)//check to see if there is a photo on file for this person
         {
            ByteArrayInputStream bis = new ByteArrayInputStream(photoBytes);
            BufferedImage photo = ImageIO.read(bis);
            photosList.add(photo);
         }
      }

      return photosList;
   }

   // ********************************************GETS A PHOTO FROM THE DB AND CONVERTS IT TO A BUFFEREDIMAGE
   public static BufferedImage getPhoto(int personId) throws SQLException, IOException, ClassNotFoundException
   {
      String sql = "select * from persons where personId = " + personId;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(sql);
      rs = (ResultSet) stmt.executeQuery();

      if (rs.next())
      {
         byte[] photoBytes = rs.getBytes("photo");//gets the person's photo as an array of bytes
         if (photoBytes != null)//check to see if there is a photo on file for this person
         {
            ByteArrayInputStream bis = new ByteArrayInputStream(photoBytes);//create a ByteArrayInputStream from our array of bytes
            BufferedImage photo = ImageIO.read(bis);//use Java's native ImageIO class, and static read method to read from our bytes, and create a BufferedImage

            DBconnection.closeResultSet();
            DBconnection.closeStatement();
            DBconnection.closeConnection();

            return photo;
         }
         else
         {
            DBconnection.closeResultSet();
            DBconnection.closeStatement();
            DBconnection.closeConnection();

            return null;
         }
      }
      else
      {
         DBconnection.closeResultSet();
         DBconnection.closeStatement();
         DBconnection.closeConnection();

         return null;
      }
   }

   // ********************************************USED TO UPDATE A PERSONS PHOTO
   public static boolean updatePhoto(int personId, BufferedImage photo) throws SQLException, IOException, ClassNotFoundException
   {
      ByteArrayOutputStream baos = new ByteArrayOutputStream();
      ImageIO.write(photo, "jpg", baos);
      baos.flush();
      byte[] photoBytes = baos.toByteArray();

      String sql = "update persons set photo = ? where personId = ?";
      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(sql);

      stmt.setBytes(1, photoBytes);
      stmt.setInt(2, personId);

      int status = stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return (status > 0);
   }

   //*********************************************GET ALL PEOPLE
   public static ArrayList<Object> getPeople() throws SQLException, ClassNotFoundException
   {
      String peoplesQuery = "SELECT p.lastName, p.firstName, p.personId From persons AS p";

      ArrayList<Object> possibleNames = new ArrayList<Object>();

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(peoplesQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      // GET PEOPLE AND STORE THEM IN AN ARRAYLIST
      while (rs.next())
      {
         ArrayList<Object> row = new ArrayList<Object>(columns);

         for (int i = 1; i <= columns; i++)
         {
            row.add(rs.getObject(i));
         }
         possibleNames.add(row);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return possibleNames;
   }

   //*********************************************GET ALL FUNDRAISERS
   public static ArrayList<Object> getFundraisers() throws SQLException, ClassNotFoundException
   {
      String fundraisersQuery = "SELECT DISTINCT p.lastName, p.firstName, p.personId FROM persons AS p, fundraisers AS f "
         + "WHERE f.personId = p.personId";

      ArrayList<Object> possibleNames = new ArrayList<Object>();

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      // GET FUNDRAISERS AND STORE THEM IN AN ARRAYLIST
      while (rs.next())
      {
         ArrayList<Object> row = new ArrayList<Object>(columns);

         for (int i = 1; i <= columns; i++)
         {
            row.add(rs.getObject(i));
         }
         possibleNames.add(row);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return possibleNames;
   }

   // ********************************************GETS A LIST OF FUNDRAISERS WHO HAVE MET THEIR GOAL IN THE LAST *MONTH*
   public static EPovertyMain.myTable getRecentGoalReachers(EPovertyMain.myTable table) throws ParseException, SQLException, ClassNotFoundException
   {
      DateFormatSymbols dfs = new DateFormatSymbols();
      String[] monthsByName = dfs.getMonths();
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");

      Calendar cal = Calendar.getInstance();
      String today = dateFormat.format(cal.getTime());
      java.util.Date todayDate = (java.util.Date) dateFormat.parse(today);
      java.sql.Date sqlToday = new java.sql.Date(todayDate.getTime());

      cal.add(Calendar.MONTH, -1);
      String monthAgo = dateFormat.format(cal.getTime());
      java.util.Date monthAgoDate = (java.util.Date) dateFormat.parse(monthAgo);
      java.sql.Date sqlDate = new java.sql.Date(monthAgoDate.getTime());

      String fundraisersQuery = "SELECT DISTINCT p.firstName, p.lastName, DATE_FORMAT(f.dateMetRaiseGoal,'%m-%d-%Y') as Date "
         + "FROM persons AS p, fundraisers AS f "
         + "WHERE f.dateMetRaiseGoal >= ? AND f.dateMetRaiseGoal <= ? "
         + "AND f.personId = p.personId";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      stmt.setDate(1, sqlDate);
      stmt.setDate(2, sqlToday);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      // GET FUNDRAISERS AND STORE THEM IN AN ARRAYLIST
      String[] columnNames =
      {
         "FUNDRAISER", "DATE"
      };
      table.setColumnIdentifiers(columnNames);

      while (rs.next())
      {
         Object[] data = new String[columns];
         //Parse out date to return the month name instead of number
         String month = rs.getString(3).substring(0, rs.getString(3).indexOf('-'));
         String year = rs.getString(3).substring(rs.getString(3).lastIndexOf('-') + 1, rs.getString(3).length());
         String day = rs.getString(3).substring(rs.getString(3).indexOf('-') + 1, rs.getString(3).lastIndexOf('-'));
         String monthName = monthsByName[Integer.parseInt(month) - 1];

         data[0] = rs.getString(1).concat(" ").concat(rs.getString(2));
         data[1] = monthName.concat(" ").concat(day).concat(", ").concat(year);

         table.addRow(data);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return table;
   }

   //*********************************************GET A GOAL FOR A SPECIFIC FUNDRAISER
   public static BigDecimal getFundraisersGoal(int personID) throws SQLException, ClassNotFoundException
   {
      String fundraisersQuery = "SELECT raiseGoal FROM fundraisers WHERE personId = " + personID;
      BigDecimal raiseGoal;
      String goal;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      rs = (ResultSet) stmt.executeQuery();

      rs.next();
      goal = rs.getString(1);
      raiseGoal = new BigDecimal(goal);

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return raiseGoal;
   }

   //*********************************************GET A TOTAL FOR A SPECIFIC FUNDRAISER
   public static BigDecimal getFundraisersTotal(int personID) throws SQLException, ClassNotFoundException
   {
      String fundraisersQuery = "SELECT raised FROM fundraisers WHERE personId = " + personID;
      BigDecimal raiseTotal;
      String total;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      rs = (ResultSet) stmt.executeQuery();

      rs.next();
      total = rs.getString(1);
      raiseTotal = new BigDecimal(total);

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return raiseTotal;
   }

   //*********************************************GET ALL FUNDRAISERS
   public static ArrayList getDonors() throws SQLException, ClassNotFoundException
   {
      String fundraisersQuery = "SELECT DISTINCT p.lastName, p.firstName, p.personId FROM persons AS p, donors AS d "
         + "WHERE d.personId = p.personId";

      ArrayList<Object> possibleNames = new ArrayList<Object>();

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      // GET FUNDRAISERS AND STORE THEM IN AN ARRAYLIST
      while (rs.next())
      {
         ArrayList<Object> row = new ArrayList<Object>(columns);

         for (int i = 1; i <= columns; i++)
         {
            row.add(rs.getObject(i));
         }
         possibleNames.add(row);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return possibleNames;
   }

   // ********************************************GET DONOR ID BASED ON PERSONID
   public static int getDonorID(int personID) throws SQLException, ClassNotFoundException
   {
      String donorQuery = "SELECT donorId FROM donors WHERE personId = " + personID;
      int donorID;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(donorQuery);
      rs = (ResultSet) stmt.executeQuery();

      rs.next();
      donorID = rs.getInt(1);

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return donorID;
   }

   // ********************************************GET FUNDRAISER ID BASED ON PERSONID
   public static int getFundraiserID(int personID) throws SQLException, ClassNotFoundException
   {
      String fundraiserQuery = "SELECT fundraiserId FROM fundraisers WHERE personId = " + personID;
      int fundraiserID;

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraiserQuery);
      rs = (ResultSet) stmt.executeQuery();

      rs.next();
      fundraiserID = rs.getInt(1);

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return fundraiserID;
   }

   //*********************************************INSERT NEW TRIP INTO EXPEDITIONS TABLE
   public static void addExpedition(String name, String description, String cost, String raiseGoal,
                                    String depart, String retrn, String cutoff) throws SQLException, ClassNotFoundException, ParseException
   {
      // CONVERT THE String to util.Date to an sql.Date
      DateFormat formatter;
      java.util.Date departDate;
      java.util.Date returnDate;
      java.util.Date cutoffDate;
      formatter = new SimpleDateFormat("yyyy-MM-dd");
      departDate = (java.util.Date) formatter.parse(depart);
      returnDate = (java.util.Date) formatter.parse(retrn);
      cutoffDate = (java.util.Date) formatter.parse(cutoff);
      java.sql.Date sqlDepart = new java.sql.Date(departDate.getTime());
      java.sql.Date sqlReturn = new java.sql.Date(returnDate.getTime());
      java.sql.Date sqlCutoff = new java.sql.Date(cutoffDate.getTime());

      String sql = "INSERT into expeditions (name, expeditionDescription, cost, defaultRaiseGoal, departDate, returnDate, cutoffDate)"
         + " values (?, ?, ?, ?, ?, ?, ?)";
      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(sql);

      stmt.setString(1, name);
      stmt.setString(2, description);
      stmt.setString(3, cost);
      stmt.setString(4, raiseGoal);
      stmt.setDate(5, sqlDepart);
      stmt.setDate(6, sqlReturn);
      stmt.setDate(7, sqlCutoff);

      stmt.executeUpdate();

      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   //*********************************************CHECKS IF PERSON IS ADMIN
   public static boolean isAdmin(int personId) throws SQLException, ClassNotFoundException
   {
      String fundraisersQuery = "SELECT a.adminId FROM admins AS a, persons AS p "
         + "WHERE p.personId = a.personId "
         + "AND p.personId = '" + personId + "'";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      rs = (ResultSet) stmt.executeQuery();

      // CHECK IF RESULT SET IS EMPTY OR NOT
      if (rs.next())
      {
         DBconnection.closeResultSet();
         DBconnection.closeStatement();
         DBconnection.closeConnection();

         return true;
      }
      else
      {
         DBconnection.closeResultSet();
         DBconnection.closeStatement();
         DBconnection.closeConnection();

         return false;
      }
   }

   // ********************************************ADD DEPOSIT TO DEPOSITS TABLE
   public static void addDeposit(int accountID, int donationID, BigDecimal amount) throws SQLException, ClassNotFoundException
   {
      String depositQuery = "INSERT into deposits (accountId, donationId, amount) values (?, ?, ?)";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(depositQuery);

      stmt.setInt(1, accountID);
      stmt.setInt(2, donationID);
      stmt.setBigDecimal(3, amount);

      stmt.executeUpdate();

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();
   }

   // ********************************************ADD DONATION TO DONATIONS TABLE
   public static int addDonation(int donorPersonID, int fundraiserPersonID, String date, BigDecimal amount) throws ParseException, SQLException, ClassNotFoundException
   {
      // CONVERT DATE INTO SQL FORMAT
      DateFormat formatter;
      java.util.Date donationDate;
      formatter = new SimpleDateFormat("yyyy-MM-dd");
      donationDate = (java.util.Date) formatter.parse(date);
      java.sql.Date sqlDonation = new java.sql.Date(donationDate.getTime());

      int donationID;
      int fundraiserID = 0;
      int donorID = DBconnection.getDonorID(donorPersonID);
      // CHECK IF THERE IS A FUNDRAISER
      if (fundraiserPersonID != 0)
      {
         fundraiserID = DBconnection.getFundraiserID(fundraiserPersonID);
      }
      String donationQuery = "INSERT into donations (donorId, fundraiserId, donationDate, donationAmount) values (?, ?, ?, ?)";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(donationQuery);

      stmt.setInt(1, donorID);
      // IF THERE IS A FUNDRAISER SET AS FUNDRAISERID OTHERWISE SET AS NULL
      if (fundraiserID != 0)
      {
         stmt.setInt(2, fundraiserID);
      }
      else
      {
         stmt.setString(2, null);
      }
      stmt.setDate(3, sqlDonation);
      stmt.setBigDecimal(4, amount);

      stmt.executeUpdate();

      rs = (ResultSet) stmt.getGeneratedKeys();
      rs.next();
      donationID = rs.getInt(1);

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return donationID;
   }

   //*********************************************CHECKS IF PERSON IS ADMIN
   public static boolean isFundraiser(int personId) throws SQLException, ClassNotFoundException
   {
      String fundraisersQuery = "SELECT f.fundraiserId FROM fundraisers AS f, persons AS p "
         + "WHERE p.personId = f.personId "
         + "AND p.personId = '" + personId + "'";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      rs = (ResultSet) stmt.executeQuery();

      // CHECK IF RESULT SET IS EMPTY OR NOT
      if (rs.next())
      {
         DBconnection.closeResultSet();
         DBconnection.closeStatement();
         DBconnection.closeConnection();

         return true;
      }
      else
      {
         DBconnection.closeResultSet();
         DBconnection.closeStatement();
         DBconnection.closeConnection();

         return false;
      }
   }

   //*********************************************CHECKS IF PERSON IS ADMIN
   public static boolean isDonor(int personId) throws SQLException, ClassNotFoundException
   {
      String fundraisersQuery = "SELECT d.donorId FROM donors AS d, persons AS p "
         + "WHERE p.personId = d.personId "
         + "AND p.personId = '" + personId + "'";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(fundraisersQuery);
      rs = (ResultSet) stmt.executeQuery();

      // CHECK IF RESULT SET IS EMPTY OR NOT
      if (rs.next())
      {
         DBconnection.closeResultSet();
         DBconnection.closeStatement();
         DBconnection.closeConnection();

         return true;
      }
      else
      {
         DBconnection.closeResultSet();
         DBconnection.closeStatement();
         DBconnection.closeConnection();

         return false;
      }
   }

   // ********************************************METHOD TO FIND THE NEXT CUTOFF DATE APPROACHING
   public static ArrayList<Object> getNextCutoffDate() throws SQLException, ClassNotFoundException, ParseException
   {
      DateFormatSymbols dfs = new DateFormatSymbols();
      String[] monthsByName = dfs.getMonths();
      ArrayList<Object> nextCutoffDate = new ArrayList<Object>();
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
      Calendar cal = Calendar.getInstance();
      String today = dateFormat.format(cal.getTime());
      java.util.Date todayDate = (java.util.Date) dateFormat.parse(today);

      // GET A FUTURE DATE FOR TESTING
      cal.add(Calendar.YEAR, 10);
      String future = dateFormat.format(cal.getTime());
      java.util.Date futureDate = (java.util.Date) dateFormat.parse(future);

      String expeditionsQuery = "SELECT name,DATE_FORMAT(cutoffDate,'%Y-%m-%d') as Date FROM expeditions";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(expeditionsQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      while (rs.next())
      {
         if ((rs.getDate(2).equals(todayDate) && rs.getDate(2).before(futureDate))
            || (rs.getDate(2).after(todayDate) && rs.getDate(2).before(futureDate)))
         {
            // THIS IS NEW DATE TO CHOOSE BETWEEN
            futureDate = rs.getDate(2);
            // CLEAR THE CURRENT CUTOFF DATE OUT
            nextCutoffDate.clear();
            ArrayList<Object> row = new ArrayList<Object>(columns);

            //Parse out date to return the month name instead of number
            String year = rs.getString(2).substring(0, rs.getString(2).indexOf('-'));
            String day = rs.getString(2).substring(rs.getString(2).lastIndexOf('-') + 1, rs.getString(2).length());
            String month = rs.getString(2).substring(rs.getString(2).indexOf('-') + 1, rs.getString(2).lastIndexOf('-'));
            String monthName = monthsByName[Integer.parseInt(month) - 1];

            String formattedDate = rs.getObject(1).toString().concat(" : " + monthName + " " + day + ", " + year);
            row.add(formattedDate);

            nextCutoffDate.add(row);
         }
      }

      return nextCutoffDate;
   }

   // ********************************************METHOD TO FIND THE NEXT DEPART DATE APPROACHING
   public static ArrayList<Object> getNextDepartDate() throws SQLException, ClassNotFoundException, ParseException
   {
      DateFormatSymbols dfs = new DateFormatSymbols();
      String[] monthsByName = dfs.getMonths();
      ArrayList<Object> nextDepartDate = new ArrayList<Object>();
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
      Calendar cal = Calendar.getInstance();
      String today = dateFormat.format(cal.getTime());
      java.util.Date todayDate = (java.util.Date) dateFormat.parse(today);

      // GET A FUTURE DATE FOR TESTING
      cal.add(Calendar.YEAR, 10);
      String future = dateFormat.format(cal.getTime());
      java.util.Date futureDate = (java.util.Date) dateFormat.parse(future);

      String expeditionsQuery = "SELECT name,DATE_FORMAT(departDate,'%Y-%m-%d') as Date FROM expeditions";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(expeditionsQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      while (rs.next())
      {
         if ((rs.getDate(2).equals(todayDate) && rs.getDate(2).before(futureDate))
            || (rs.getDate(2).after(todayDate) && rs.getDate(2).before(futureDate)))
         {
            // THIS IS NEW DATE TO CHOOSE BETWEEN
            futureDate = rs.getDate(2);
            // CLEAR THE CURRENT CUTOFF DATE OUT
            nextDepartDate.clear();
            ArrayList<Object> row = new ArrayList<Object>(columns);

            //Parse out date to return the month name instead of number
            String year = rs.getString(2).substring(0, rs.getString(2).indexOf('-'));
            String day = rs.getString(2).substring(rs.getString(2).lastIndexOf('-') + 1, rs.getString(2).length());
            String month = rs.getString(2).substring(rs.getString(2).indexOf('-') + 1, rs.getString(2).lastIndexOf('-'));
            String monthName = monthsByName[Integer.parseInt(month) - 1];

            String formattedDate = rs.getObject(1).toString().concat(" : " + monthName + " " + day + ", " + year);
            row.add(formattedDate);

            nextDepartDate.add(row);
         }
      }

      return nextDepartDate;
   }

   // ********************************************METHOD TO FIND THE NEXT RETURN DATE APPROACHING
   public static ArrayList<Object> getNextReturnDate() throws SQLException, ClassNotFoundException, ParseException
   {
      DateFormatSymbols dfs = new DateFormatSymbols();
      String[] monthsByName = dfs.getMonths();
      ArrayList<Object> nextReturnDate = new ArrayList<Object>();
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
      Calendar cal = Calendar.getInstance();
      String today = dateFormat.format(cal.getTime());
      java.util.Date todayDate = (java.util.Date) dateFormat.parse(today);

      // GET A FUTURE DATE FOR TESTING
      cal.add(Calendar.YEAR, 10);
      String future = dateFormat.format(cal.getTime());
      java.util.Date futureDate = (java.util.Date) dateFormat.parse(future);

      String expeditionsQuery = "SELECT name,DATE_FORMAT(returnDate,'%Y-%m-%d') as Date FROM expeditions";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(expeditionsQuery);
      rs = (ResultSet) stmt.executeQuery();
      ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();
      int columns = md.getColumnCount();

      while (rs.next())
      {
         if ((rs.getDate(2).equals(todayDate) && rs.getDate(2).before(futureDate))
            || (rs.getDate(2).after(todayDate) && rs.getDate(2).before(futureDate)))
         {
            // THIS IS NEW DATE TO CHOOSE BETWEEN
            futureDate = rs.getDate(2);
            // CLEAR THE CURRENT CUTOFF DATE OUT
            nextReturnDate.clear();
            ArrayList<Object> row = new ArrayList<Object>(columns);

            //Parse out date to return the month name instead of number
            String year = rs.getString(2).substring(0, rs.getString(2).indexOf('-'));
            String day = rs.getString(2).substring(rs.getString(2).lastIndexOf('-') + 1, rs.getString(2).length());
            String month = rs.getString(2).substring(rs.getString(2).indexOf('-') + 1, rs.getString(2).lastIndexOf('-'));
            String monthName = monthsByName[Integer.parseInt(month) - 1];

            String formattedDate = rs.getObject(1).toString().concat(" : " + monthName + " " + day + ", " + year);
            row.add(formattedDate);

            nextReturnDate.add(row);
         }
      }

      return nextReturnDate;
   }

   // ********************************************GETS THE TOTAL NUMBER OF TRIPS PLANNED
   public static int getTripCount() throws SQLException, ClassNotFoundException
   {
      String expeditionsQuery = "SELECT expeditionId FROM expeditions";

      conn = DBconnection.getConnection();
      stmt = (PreparedStatement) conn.prepareStatement(expeditionsQuery);
      rs = (ResultSet) stmt.executeQuery();

      while (rs.next())
      {
         String id = rs.getString(1);
      }
      rs.last();
      int rowCount = rs.getRow();

      return rowCount;
   }

   //*********************************************GET THE SPECIFIED NUMBER OF MONTHS
   public static Object[] getMonths(int numberOfMonths)
   {
      Object[] months = new Object[numberOfMonths];

      DateFormat dateFormat = new SimpleDateFormat("MM");
      DateFormatSymbols dfs = new DateFormatSymbols();
      String[] monthsByName = dfs.getMonths();

      Calendar cal = Calendar.getInstance();

      for (int x = 0; x < numberOfMonths; x++)
      {
         months[numberOfMonths - x - 1] = monthsByName[Integer.parseInt(dateFormat.format(cal.getTime())) - 1];
         cal.add(Calendar.MONTH, -1);
      }

      return months;
   }

   //*********************************************GET THE AMOUNT OF DONATIONS FOR THE REVEIVED MONTHS
   public static Object[] getMonthlyDonations(Object[] months) throws SQLException, ClassNotFoundException
   {
      // GET THE NUMBER OF MONTHS TO ADD UP
      int numberOfMonths = months.length;

      // DOUBLE FOR DONATION TOTAL FOR EACH MONTH
      double totalDonations = 0;

      // WHERE THE DATA WILL BE STORED AND EVENTUALLY RETURNED
      Object[] donations = new Object[numberOfMonths];

      // GET A CONNECTION
      conn = DBconnection.getConnection();

      // STRING FOR QUERY
      String donationsQuery = "SELECT donationAmount FROM donations WHERE donationDate "
         + "BETWEEN ? AND ?";

      // DATE FORMAT AND CALENDAR OBJECTS
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
      Calendar cal = Calendar.getInstance();

      // RUN THROUGH EACH MONTH, QUERYING THE DB AND ADDING UP THE DONATIONS FOR THAT MONTH
      for (int x = 0; x < numberOfMonths; x++)
      {
         // GET THE YEAR AND MONTH AND THE MAX DAYS FOR THE MONTH
         String yearMonth = dateFormat.format(cal.getTime());
         int daysMax = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
         String firstOfMonth = yearMonth + "-1";
         String lastOfMonth = yearMonth + "-" + daysMax;

         // PREPARE THE STATEMENT AND EXECUTE IT
         stmt = (PreparedStatement) conn.prepareStatement(donationsQuery);
         stmt.setString(1, firstOfMonth);
         stmt.setString(2, lastOfMonth);
         rs = (ResultSet) stmt.executeQuery();

         // RUN THROUGH RESULTS AND ADD THEM UP
         while (rs.next())
         {
            totalDonations += Double.parseDouble(rs.getString(1));
         }

         // ADD THE DATA TO THE OBJECT ARRAY, RESET totalDonations, and SUBTRACT A MONTH
         donations[numberOfMonths - x - 1] = totalDonations;
         totalDonations = 0;
         cal.add(Calendar.MONTH, -1);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return donations;
   }

   //*********************************************GET THE AMOUNT OF DONATIONS FOR THE REVEIVED MONTHS
   public static Object[] getMonthlyWithdrawals(Object[] months) throws SQLException, ClassNotFoundException
   {
      // GET THE NUMBER OF MONTHS TO ADD UP
      int numberOfMonths = months.length;

      // DOUBLE FOR WITHDRAWAL TOTAL FOR EACH MONTH
      double totalWithdrawals = 0;

      // WHERE THE DATA WILL BE STORED AND EVENTUALLY RETURNED
      Object[] withdrawals = new Object[numberOfMonths];

      // GET A CONNECTION
      conn = DBconnection.getConnection();

      // STRING FOR QUERY
      String donationsQuery = "SELECT amount FROM withdrawals WHERE withdrawalDate "
         + "BETWEEN ? AND ?";

      // DATE FORMAT AND CALENDAR OBJECTS
      DateFormat dateFormat = new SimpleDateFormat("yyyy-MM");
      Calendar cal = Calendar.getInstance();

      // RUN THROUGH EACH MONTH, QUERYING THE DB AND ADDING UP THE DONATIONS FOR THAT MONTH
      for (int x = 0; x < numberOfMonths; x++)
      {
         // GET THE YEAR AND MONTH AND THE MAX DAYS FOR THE MONTH
         String yearMonth = dateFormat.format(cal.getTime());
         int daysMax = cal.getActualMaximum(Calendar.DAY_OF_MONTH);
         String firstOfMonth = yearMonth + "-1";
         String lastOfMonth = yearMonth + "-" + daysMax;

         // PREPARE THE STATEMENT AND EXECUTE IT
         stmt = (PreparedStatement) conn.prepareStatement(donationsQuery);
         stmt.setString(1, firstOfMonth);
         stmt.setString(2, lastOfMonth);
         rs = (ResultSet) stmt.executeQuery();

         // RUN THROUGH RESULTS AND ADD THEM UP
         while (rs.next())
         {
            totalWithdrawals += Double.parseDouble(rs.getString(1));
         }

         // ADD THE DATA TO THE OBJECT ARRAY, RESET totalDonations, and SUBTRACT A MONTH
         withdrawals[numberOfMonths - x - 1] = totalWithdrawals;
         totalWithdrawals = 0;
         cal.add(Calendar.MONTH, -1);
      }

      DBconnection.closeResultSet();
      DBconnection.closeStatement();
      DBconnection.closeConnection();

      return withdrawals;
   }
}
